Importando as bibliotecas necessárias
library(tidyverse)
Registered S3 methods overwritten by 'dbplyr':
method from
print.tbl_lazy
print.tbl_sql
-- Attaching packages ------------------------------------------------------- tidyverse 1.3.1 --
v ggplot2 3.3.5 v purrr 0.3.4
v tibble 3.1.5 v dplyr 1.0.7
v tidyr 1.1.4 v stringr 1.4.0
v readr 2.0.2 v forcats 0.5.1
-- Conflicts ---------------------------------------------------------- tidyverse_conflicts() --
x dplyr::filter() masks stats::filter()
x dplyr::lag() masks stats::lag()
library(readxl)
library(plotly)
Attaching package: ‘plotly’
The following object is masked from ‘package:ggplot2’:
last_plot
The following object is masked from ‘package:stats’:
filter
The following object is masked from ‘package:graphics’:
layout
library(gmodels)
library(sqldf)
Carregando pacotes exigidos: gsubfn
Carregando pacotes exigidos: proto
Carregando pacotes exigidos: RSQLite
Carregando o arquivo Cadastral para fazer as análises
ds <- read_excel("cadastral.xlsx")
ds
- Tire uma tabela de frequência usando a função table na variável Sexo. Quantos homens e quantas mulheres têm no arquivo?
table(ds$Sexo)
Feminino Masculino
432 514
- Ordenar a variável ID.
ds <- ds[order(ds$ID),]
ds
- Remova os ID duplicados. Coloque esse arquivo dentro de um objeto chamado A.
A <- unique(ds)
A
- Já no objeto A. Tire uma tabela de frequência usando a função table na variável Sexo. Quantos homens e quantas mulheres têm no arquivo?
table(A$Sexo)
Feminino Masculino
216 257
- Crie uma variável data atual e acrescenta essa variável ao objeto/ arquivo A.
A$data_atual <- Sys.Date()
A
- Verifique se a variável salario é numérica?
is.numeric(A$salario)
[1] TRUE
- Mostre o mínimo e o máximo da variável salario.
print(sprintf("Mínimo salário -> %0.2f", min(A$salario)))
[1] "Mínimo salário -> 1575.00"
print(sprintf("Máximo salário -> %0.2f", max(A$salario)))
[1] "Máximo salário -> 13500.00"
- Crie uma variável faixa de salario com as seguintes quebras: 1574, 3000, 5000, 7000, 13500.
A$faixa_salario <- cut(A$salario, c(1574, 3000, 5000, 7000, 13500),label=c("A","B","C","D"))
A
- Crie um visualizador/ matriz usando a função View(A). Exatamente esse comando.
View(A)
- Atribua o arquivo Transacional ao objeto B. E crie um visualizador/ matriz usando a função View(B).
B <- read_excel("transacional.xlsx")
View(B)
- Crie um objeto chamado consolidado e faça uma união dos arquivos A e B através do Left join. Usando a função do R.
consolidado <- merge(A,B, by='ID', all.x=T)
consolidado
- Crie uma variável comprometimento de renda usando as variáveis ValorEmprestimo e Salario. Para isso utilize a expressão. (ValorEmprestimo / salario). Quantas variáveis ficaram no arquivo?
consolidado$comprometido_de_renda <- (consolidado$ValorEmprestimo / consolidado$salario)
print(sprintf("Número de colunas -> %0.0f", ncol(consolidado)))
[1] "Número de colunas -> 18"
- Faça um gráfico de pie usando o pacote plotly. Pode usar qualquer variáveis Explique o gráfico.
fig <- plot_ly(consolidado, labels = ~faixa_salario, values = ~salario, type = 'pie')
fig <- fig %>% layout(title = 'Faixa salarial',
xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
fig
#Criei um gráfico de pizza com a variável de faixa salarial:
# 39,2% das pessoas pertecem ao grupo A ([1500 - 3000[)
# 30,7% das pessoas pertecem ao grupo B ([3000 - 5000[)
# 17,7% das pessoas pertecem ao grupo C ([5000 - 7000[)
# 12,4% das pessoas pertecem ao grupo D ([7000 - 13500[)
# Temos praticamente 70% das pessoas com renda salarial de até 5000,00 reais formados pela class A e # B
- Faça um tabela cruzada usando a função Crosstable do packege gmodels das variáveis sexo e default. Olhando para percentual coluna, quem tem o maior % de inadimplência H ou M? e qual é esse valor.
Cross <- CrossTable(consolidado$Sexo, consolidado$default)
Cell Contents
|-------------------------|
| N |
| Chi-square contribution |
| N / Row Total |
| N / Col Total |
| N / Table Total |
|-------------------------|
Total Observations in Table: 473
| consolidado$default
consolidado$Sexo | Adimplente | Inadimplente | Row Total |
-----------------|--------------|--------------|--------------|
Feminino | 49 | 167 | 216 |
| 1.810 | 0.686 | |
| 0.227 | 0.773 | 0.457 |
| 0.377 | 0.487 | |
| 0.104 | 0.353 | |
-----------------|--------------|--------------|--------------|
Masculino | 81 | 176 | 257 |
| 1.521 | 0.577 | |
| 0.315 | 0.685 | 0.543 |
| 0.623 | 0.513 | |
| 0.171 | 0.372 | |
-----------------|--------------|--------------|--------------|
Column Total | 130 | 343 | 473 |
| 0.275 | 0.725 | |
-----------------|--------------|--------------|--------------|
print("Quem tem o maior precentual de inadiplência são os homens com 51,30%")
[1] "Quem tem o maior precentual de inadiplência são os homens com 51,30%"
- Utilizando a biblioteca SQLDF pede-se:
- Selecione da tabela consolidado os registros do sexo masculino e que possui um conta particular.
- Tira a média de QtdaPagas pela variável Atraso .
sexo_masculino_particular <- sqldf("select * from consolidado where sexo = 'Masculino' and conta = 'Particular'")
sexo_masculino_particular
QtdaPagas_vs_Atraso <- sqldf("select Atraso, avg(QtdaPagas) as media_QtdaPagas from consolidado group by Atraso")
QtdaPagas_vs_Atraso
- Calcule a expressão numérica: (((√16)/2)3^2)/2(9-2^3),
calculo1 = ((sqrt(16)/2)*3^2)/2*(9-2^3)
calculo1
[1] 9
- Calcule a expressão numérica: -(-23)+(-〖1)〗0-(√(25−3^2 ))-5^3/25
calculo2 <- -(-2^3) + (-1)^0 - sqrt(25 - ( 3 ^ 2)) - ( (5 ^ 3) / 25 )
calculo2
Salvando o Arquivo
LS0tDQp0aXRsZTogIlRyYWJhbGhvIGRlIFIgLSBGSUFQIg0Kb3V0cHV0OiBodG1sX25vdGVib29rDQotLS0NCg0KSW1wb3J0YW5kbyBhcyBiaWJsaW90ZWNhcyBuZWNlc3PDoXJpYXMNCg0KYGBge3J9DQpsaWJyYXJ5KHRpZHl2ZXJzZSkNCmxpYnJhcnkocmVhZHhsKQ0KbGlicmFyeShwbG90bHkpDQpsaWJyYXJ5KGdtb2RlbHMpDQpsaWJyYXJ5KHNxbGRmKQ0KYGBgDQoNCkNhcnJlZ2FuZG8gbyBhcnF1aXZvIENhZGFzdHJhbCBwYXJhIGZhemVyIGFzIGFuw6FsaXNlcw0KDQpgYGB7cn0NCmRzIDwtIHJlYWRfZXhjZWwoImNhZGFzdHJhbC54bHN4IikNCmRzDQpgYGANCg0KMSkgVGlyZSB1bWEgdGFiZWxhIGRlIGZyZXF1w6puY2lhIHVzYW5kbyBhIGZ1bsOnw6NvIHRhYmxlIG5hIHZhcmnDoXZlbCBTZXhvLiBRdWFudG9zIGhvbWVucyBlIHF1YW50YXMgbXVsaGVyZXMgdMOqbSBubyBhcnF1aXZvPw0KDQpgYGB7cn0NCnRhYmxlKGRzJFNleG8pDQpgYGANCjIpIE9yZGVuYXIgYSB2YXJpw6F2ZWwgSUQuDQoNCmBgYHtyfQ0KZHMgPC0gZHNbb3JkZXIoZHMkSUQpLF0NCmRzDQpgYGANCg0KMykgUmVtb3ZhIG9zIElEIGR1cGxpY2Fkb3MuIENvbG9xdWUgZXNzZSBhcnF1aXZvIGRlbnRybyBkZSB1bSBvYmpldG8gY2hhbWFkbyBBLg0KDQpgYGB7cn0NCkEgPC0gdW5pcXVlKGRzKQ0KQQ0KYGBgDQoNCjQpIErDoSBubyBvYmpldG8gQS4gVGlyZSB1bWEgdGFiZWxhIGRlIGZyZXF1w6puY2lhIHVzYW5kbyBhIGZ1bsOnw6NvIHRhYmxlIG5hIHZhcmnDoXZlbCBTZXhvLiBRdWFudG9zIGhvbWVucyBlIHF1YW50YXMgbXVsaGVyZXMgdMOqbSBubyBhcnF1aXZvPw0KDQpgYGB7cn0NCnRhYmxlKEEkU2V4bykNCmBgYA0KNSkgQ3JpZSB1bWEgdmFyacOhdmVsIGRhdGEgYXR1YWwgZSBhY3Jlc2NlbnRhIGVzc2EgdmFyacOhdmVsIGFvIG9iamV0by8gYXJxdWl2byBBLg0KDQpgYGB7cn0NCkEkZGF0YV9hdHVhbCA8LSBTeXMuRGF0ZSgpDQpBDQpgYGANCjYpIFZlcmlmaXF1ZSBzZSBhIHZhcmnDoXZlbCBzYWxhcmlvIMOpIG51bcOpcmljYT8NCg0KYGBge3J9DQppcy5udW1lcmljKEEkc2FsYXJpbykNCmBgYA0KNykgTW9zdHJlIG8gbcOtbmltbyBlIG8gbcOheGltbyBkYSB2YXJpw6F2ZWwgc2FsYXJpby4NCg0KYGBge3J9DQpwcmludChzcHJpbnRmKCJNw61uaW1vIHNhbMOhcmlvIC0+ICUwLjJmIiwgbWluKEEkc2FsYXJpbykpKQ0KcHJpbnQoc3ByaW50ZigiTcOheGltbyBzYWzDoXJpbyAtPiAlMC4yZiIsIG1heChBJHNhbGFyaW8pKSkNCmBgYA0KDQo4KSBDcmllIHVtYSB2YXJpw6F2ZWwgZmFpeGEgZGUgc2FsYXJpbyBjb20gYXMgc2VndWludGVzIHF1ZWJyYXM6IDE1NzQsIDMwMDAsIDUwMDAsIDcwMDAsIDEzNTAwLg0KDQpgYGB7cn0NCkEkZmFpeGFfc2FsYXJpbyA8LSBjdXQoQSRzYWxhcmlvLCBjKDE1NzQsIDMwMDAsIDUwMDAsIDcwMDAsIDEzNTAwKSxsYWJlbD1jKCJBIiwiQiIsIkMiLCJEIikpDQpBDQpgYGANCjkpIENyaWUgdW0gdmlzdWFsaXphZG9yLyBtYXRyaXogdXNhbmRvIGEgZnVuw6fDo28gVmlldyhBKS4gRXhhdGFtZW50ZSBlc3NlIGNvbWFuZG8uDQpgYGB7cn0NClZpZXcoQSkNCmBgYA0KDQoxMCkgQXRyaWJ1YSBvIGFycXVpdm8gVHJhbnNhY2lvbmFsIGFvIG9iamV0byBCLiBFIGNyaWUgdW0gdmlzdWFsaXphZG9yLyBtYXRyaXogdXNhbmRvIGEgZnVuw6fDo28gVmlldyhCKS4NCg0KYGBge3J9DQpCIDwtIHJlYWRfZXhjZWwoInRyYW5zYWNpb25hbC54bHN4IikNClZpZXcoQikNCmBgYA0KDQoxMSkgQ3JpZSB1bSBvYmpldG8gY2hhbWFkbyBjb25zb2xpZGFkbyBlIGZhw6dhIHVtYSB1bmnDo28gZG9zIGFycXVpdm9zIEEgZSBCIGF0cmF2w6lzIGRvIExlZnQgam9pbi4gVXNhbmRvIGEgZnVuw6fDo28gZG8gUi4NCg0KYGBge3J9DQpjb25zb2xpZGFkbyA8LSBtZXJnZShBLEIsIGJ5PSdJRCcsIGFsbC54PVQpDQpjb25zb2xpZGFkbw0KYGBgDQoxMikgQ3JpZSB1bWEgdmFyacOhdmVsIGNvbXByb21ldGltZW50byBkZSByZW5kYSB1c2FuZG8gYXMgdmFyacOhdmVpcyBWYWxvckVtcHJlc3RpbW8gZSBTYWxhcmlvLiAgUGFyYSBpc3NvIHV0aWxpemUgYSBleHByZXNzw6NvLiAoVmFsb3JFbXByZXN0aW1vIC8gc2FsYXJpbykuIFF1YW50YXMgdmFyacOhdmVpcyBmaWNhcmFtIG5vIGFycXVpdm8/DQoNCmBgYHtyfQ0KY29uc29saWRhZG8kY29tcHJvbWV0aWRvX2RlX3JlbmRhIDwtIChjb25zb2xpZGFkbyRWYWxvckVtcHJlc3RpbW8gLyBjb25zb2xpZGFkbyRzYWxhcmlvKQ0KcHJpbnQoc3ByaW50ZigiTsO6bWVybyBkZSBjb2x1bmFzIC0+ICUwLjBmIiwgbmNvbChjb25zb2xpZGFkbykpKQ0KYGBgDQoNCjEzKSBGYcOnYSB1bSBncsOhZmljbyBkZSBwaWUgdXNhbmRvIG8gcGFjb3RlIHBsb3RseS4gUG9kZSB1c2FyIHF1YWxxdWVyIHZhcmnDoXZlaXMgRXhwbGlxdWUgbyBncsOhZmljby4NCg0KDQpgYGB7cn0NCmZpZyA8LSBwbG90X2x5KGNvbnNvbGlkYWRvLCBsYWJlbHMgPSB+ZmFpeGFfc2FsYXJpbywgdmFsdWVzID0gfnNhbGFyaW8sIHR5cGUgPSAncGllJykNCmZpZyA8LSBmaWcgJT4lIGxheW91dCh0aXRsZSA9ICdGYWl4YSBzYWxhcmlhbCcsDQogICAgICAgICB4YXhpcyA9IGxpc3Qoc2hvd2dyaWQgPSBGQUxTRSwgemVyb2xpbmUgPSBGQUxTRSwgc2hvd3RpY2tsYWJlbHMgPSBGQUxTRSksDQogICAgICAgICB5YXhpcyA9IGxpc3Qoc2hvd2dyaWQgPSBGQUxTRSwgemVyb2xpbmUgPSBGQUxTRSwgc2hvd3RpY2tsYWJlbHMgPSBGQUxTRSkpDQoNCmZpZw0KYGBgDQpgYGB7cn0NCiNDcmllaSB1bSBncsOhZmljbyBkZSBwaXp6YSBjb20gYSB2YXJpw6F2ZWwgZGUgZmFpeGEgc2FsYXJpYWw6DQojICAgICAgMzksMiUgZGFzIHBlc3NvYXMgcGVydGVjZW0gYW8gZ3J1cG8gQSAoWzE1MDAgLSAzMDAwWykNCiMgICAgICAzMCw3JSBkYXMgcGVzc29hcyBwZXJ0ZWNlbSBhbyBncnVwbyBCIChbMzAwMCAtIDUwMDBbKQ0KIyAgICAgIDE3LDclIGRhcyBwZXNzb2FzIHBlcnRlY2VtIGFvIGdydXBvIEMgKFs1MDAwIC0gNzAwMFspDQojICAgICAgMTIsNCUgZGFzIHBlc3NvYXMgcGVydGVjZW0gYW8gZ3J1cG8gRCAoWzcwMDAgLSAxMzUwMFspDQojIFRlbW9zIHByYXRpY2FtZW50ZSA3MCUgZGFzIHBlc3NvYXMgY29tIHJlbmRhIHNhbGFyaWFsIGRlIGF0w6kgNTAwMCwwMCByZWFpcyBmb3JtYWRvcyBwZWxhIGNsYXNzIEEgZSAjIEINCmBgYA0KDQoxNCkgRmHDp2EgdW0gIHRhYmVsYSBjcnV6YWRhIHVzYW5kbyBhIGZ1bsOnw6NvIENyb3NzdGFibGUgZG8gcGFja2VnZSBnbW9kZWxzIGRhcyB2YXJpw6F2ZWlzIHNleG8gZSBkZWZhdWx0LiBPbGhhbmRvIHBhcmEgcGVyY2VudHVhbCBjb2x1bmEsIHF1ZW0gdGVtIG8gbWFpb3IgJSBkZSBpbmFkaW1wbMOqbmNpYSBIIG91IE0/IGUgcXVhbCDDqSBlc3NlIHZhbG9yLg0KDQpgYGB7cn0NCkNyb3NzIDwtIENyb3NzVGFibGUoY29uc29saWRhZG8kU2V4bywgY29uc29saWRhZG8kZGVmYXVsdCkNCmBgYA0KYGBge3J9DQpwcmludCgiUXVlbSB0ZW0gbyBtYWlvciBwcmVjZW50dWFsIGRlIGluYWRpcGzDqm5jaWEgc8OjbyBvcyBob21lbnMgY29tIDUxLDMwJSIpDQpgYGANCjE1KSBVdGlsaXphbmRvIGEgYmlibGlvdGVjYSBTUUxERiBwZWRlLXNlOg0KICAgICAgIGEpIFNlbGVjaW9uZSBkYSB0YWJlbGEgY29uc29saWRhZG8gb3MgcmVnaXN0cm9zIGRvIHNleG8gbWFzY3VsaW5vIGUgcXVlIHBvc3N1aSB1bSBjb250YSBwYXJ0aWN1bGFyLg0KICAgICAgIGIpIFRpcmEgYSBtw6lkaWEgZGUgUXRkYVBhZ2FzIHBlbGEgdmFyacOhdmVsIEF0cmFzbyAuDQoNCmBgYHtyfQ0Kc2V4b19tYXNjdWxpbm9fcGFydGljdWxhciA8LSBzcWxkZigic2VsZWN0ICogZnJvbSBjb25zb2xpZGFkbyB3aGVyZSBzZXhvID0gJ01hc2N1bGlubycgYW5kIGNvbnRhID0gJ1BhcnRpY3VsYXInIikNCnNleG9fbWFzY3VsaW5vX3BhcnRpY3VsYXINCmBgYA0KYGBge3J9DQpRdGRhUGFnYXNfdnNfQXRyYXNvIDwtIHNxbGRmKCJzZWxlY3QgQXRyYXNvLCBhdmcoUXRkYVBhZ2FzKSBhcyBtZWRpYV9RdGRhUGFnYXMgZnJvbSBjb25zb2xpZGFkbyBncm91cCBieSBBdHJhc28iKQ0KUXRkYVBhZ2FzX3ZzX0F0cmFzbw0KYGBgICAgICAgDQoNCjE2KSBDYWxjdWxlIGEgZXhwcmVzc8OjbyBudW3DqXJpY2E6DQoJKCgo4oiaMTYpLzIpKjNeMikvMiooOS0yXjMpLA0KCQ0KYGBge3J9DQpjYWxjdWxvMSA9ICgoc3FydCgxNikvMikqM14yKS8yKig5LTJeMykNCmNhbGN1bG8xDQpgYGANCjE3KSBDYWxjdWxlIGEgZXhwcmVzc8OjbyBudW3DqXJpY2E6DQoJLSgtMl4zKSsoLeOAljEp44CXXjAtKOKImigyNeKIkjNeMiApKS01XjMvMjUNCg0KYGBge3J9DQpjYWxjdWxvMiA8LSAtKC0yXjMpICsgKC0xKV4wIC0gc3FydCgyNSAtICggMyBeIDIpKSAtICggKDUgXiAzKSAvIDI1ICkNCmNhbGN1bG8yDQpgYGAgICAgICAgDQoNClNhbHZhbmRvIG8gQXJxdWl2bw0KDQpgYGB7cn0gDQp3cml0ZV9leGNlbF9jc3YyKGNvbnNvbGlkYWRvLCBmaWxlPSJjb25zb2xpZGFkb19wYXJhX2FuYWxpc2UuY3N2IikNCmNvbnNvbGlkYWRvIA0KYGBgDQoNCg==